﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Data.SqlTypes;


namespace DoAnASP
{
    public class DAO_SanPham
    {

        DAO_dataProvider kn = new DAO_dataProvider();

        public DataTable TimKiemTheoThuongHieu(int id)
        {
            DataTable dt = new DataTable();
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql = "select * from SanPham where idthuonghieu="+id;
            SqlCommand cmd = new SqlCommand(sql, kn.conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            cmd.ExecuteNonQuery();
            kn.Close_Connection();
            return dt;
        }

        public DataTable TimKiemTheoten (String name)
        {
            DataTable dt = new DataTable();
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql = "select * from SanPham where TenSP like N'%" + name + "%'";
            SqlCommand cmd = new SqlCommand(sql,kn.conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            cmd.ExecuteNonQuery();
            kn.Close_Connection();
            return dt;
        }

        public DataTable TiemTheoGia(int ID)
        {
            DataTable dt = new DataTable();
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            SqlDataAdapter da = new SqlDataAdapter();
             SqlCommand cmd = new SqlCommand();
            string sql ="";
            switch (ID)
            {
                // // gia từ 8 - 12 triệu
                case 1:
                    sql = "select * from SanPham Where Gia between 8000000 and 12000000";
                    cmd = new SqlCommand(sql, kn.conn);
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                    break;
                // // gia từ 12 - 15 triệu
                case 2:
                    sql = "select * from SanPham Where Gia between 12000000 and 15000000";
                    cmd = new SqlCommand(sql, kn.conn);
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                    break;
                // // gia từ 15 - 20 triệu
                case 3:
                    sql = "select * from SanPham Where Gia between 15000000 and 20000000";
                     cmd = new SqlCommand(sql, kn.conn);
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                    break;
                // // gia trên 20 triệu
                case 4:
                    sql = "select * from SanPham Where Gia > 20000000";
                    cmd = new SqlCommand(sql, kn.conn);
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                    break;

            }

            
            kn.Close_Connection();
            return dt;
        }

       
        public DataTable LoadSanPham ()
        {
            DataTable dt = new DataTable();
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql1 = "select * from SanPham order by MaSP desc";
            SqlCommand cmd = new SqlCommand(sql1, kn.conn);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(dt);
            kn.Close_Connection();
            return dt;

        }

        public DataTable LoadSanPham_top()
        {
            DataTable dt = new DataTable();
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql1 = "select top 8 * from SanPham order by MaSP desc";
            SqlCommand cmd = new SqlCommand(sql1, kn.conn);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(dt);
            kn.Close_Connection();
            return dt;
        }

        public DataTable LoadSanPham_masp(int masp)
        {
            DataTable dt = new DataTable();
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql1 = "select * from SanPham where MaSP=@MaSP";
            SqlCommand cmd = new SqlCommand(sql1, kn.conn);
            cmd.Parameters.Add("@MaSP", SqlDbType.Int).Value = masp;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(dt);
            kn.Close_Connection();
            return dt;
        }

        // /// dung cho danh mục thương hiệu
        // // load những sản phảm thương hiệu lên
        public DataTable LoadSanPham_ThuongHieu(int IDTH)
        {
            DataTable dt = new DataTable();
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql1 = "select * from SanPham where MaSP=@IDTH";
            SqlCommand cmd = new SqlCommand(sql1, kn.conn);
            cmd.Parameters.Add("@IDTH", SqlDbType.Int).Value = IDTH;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(dt);
            kn.Close_Connection();
            return dt;
        }
        /// <summary>
        /// 
        /// </summary>
        ///         Dim sql As String
        //sql = "Insert into Sach (TenSach, idTheLoai, Gia, DaXoa, NgayNhap, idTacGia,SoLuong) values (?, ?, ?, ?, ?, ?, ?)"
        //'B4: Thuc thi chuoi strSQL
        //Dim cmd As New OleDbCommand(sql, cn)
        //cmd.Parameters.Add("@TenSach", OleDbType.WChar)
        //cmd.Parameters.Add("@idTheLoai", OleDbType.Integer)
        //cmd.Parameters.Add("@Gia", OleDbType.WChar)
        //cmd.Parameters.Add("@DaXoa", OleDbType.Integer)
        //cmd.Parameters.Add("@NgayNhap", OleDbType.Date)
        //cmd.Parameters.Add("@idTacGia", OleDbType.WChar)
        //cmd.Parameters.Add("@SoLuong", OleDbType.Integer)

        //cmd.Parameters("@TenSach").Value = sach.LayTenSach
        //cmd.Parameters("@idTheLoai").Value = sach.LayIDTheLoai
        //cmd.Parameters("@Gia").Value = sach.LayGiaSach
        //cmd.Parameters("@DaXoa").Value = 0
        //cmd.Parameters("@NgayNhap").Value = sach.LayNgayNhap
        //cmd.Parameters("@idTacGia").Value = sach.layTacGia
        //cmd.Parameters("@SoLuong").Value = sach.LaySoLuong

        //cmd.ExecuteNonQuery()
        //sql = "Select @@IDENTITY"
        //cmd = New OleDbCommand(sql, cn)
        //sach.LayIdSach = cmd.ExecuteScalar()
        //cts.LayIDSach = sach.LayIdSach
        //Dim chitiet As New ChiTietSachDAO
        //chitiet.ThemChiTietSach(cts)
        /// <param name="sp"></param>
        /// <returns></returns>
        public float LayGiaSP( DTO_SanPham sp)
        {
            float gia;

            String sql3 = "select * from SanPham where IDSP = '" + sp.IDSanPham + "' ";
            SqlCommand cmd = new SqlCommand(sql3, kn.conn);
            SqlDataAdapter da = new SqlDataAdapter(sql3, kn.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);

            gia = (float)dt.Rows[0]["Gia"];
            cmd.Dispose();
            kn.Close_Connection();

            return gia;
        }

        public void ThemThongTinSp ( DTO_ThongTinSP tt)
        {          
            kn.ChuoiKetNoi();
            kn.Open_Connection();

            string sql2 = "insert into ThongTinSP (CongNgheCPU,MaSP, LoaiCPU, DungLuongRAM, DungLuongODia, KichThuocMH, CardDoHoa, KetNoiMang,TichHop, HeDieuHanh, TrongLuong, ThongTinPin,KhuyenMai,XuatXu,ThoiGianBH,Keyboard) values (@CongNgheCPU, @MaSP, @LoaiCPU, @DungLuongRAM, @DungLuongODia, @KichThuocMH, @CardDoHoa, @KetNoiMang,@TichHop, @HeDieuHanh, @TrongLuong, @ThongTinPin,@KhuyenMai,@XuatXu,@ThoiGianBH,@Keyboard)";
            SqlCommand cmd = new SqlCommand(sql2,kn.conn);

            cmd.Parameters.Add("@CongNgheCPU", SqlDbType.NVarChar);
            cmd.Parameters.Add("@MaSP", SqlDbType.Int);
            cmd.Parameters.Add("@LoaiCPU", SqlDbType.NVarChar);
            cmd.Parameters.Add("@DungLuongRAM", SqlDbType.NVarChar);
            cmd.Parameters.Add("@DungLuongODia", SqlDbType.NVarChar);
            cmd.Parameters.Add("@KichThuocMH", SqlDbType.NVarChar);
            cmd.Parameters.Add("@CardDoHoa", SqlDbType.NVarChar);
            cmd.Parameters.Add("@KetNoiMang", SqlDbType.NVarChar);
            cmd.Parameters.Add("@TichHop", SqlDbType.NVarChar);
            cmd.Parameters.Add("@HeDieuHanh", SqlDbType.NVarChar);
            cmd.Parameters.Add("@TrongLuong", SqlDbType.NVarChar);
            cmd.Parameters.Add("@ThongTinPin", SqlDbType.NVarChar);
            cmd.Parameters.Add("@KhuyenMai", SqlDbType.NVarChar);
            cmd.Parameters.Add("@XuatXu", SqlDbType.NVarChar);
            cmd.Parameters.Add("@ThoiGianBH", SqlDbType.NVarChar);
            cmd.Parameters.Add("@Keyboard", SqlDbType.NVarChar);
            //cmd.Parameters.Add("@ImageSP", SqlDbType.NVarChar);


            cmd.Parameters["@CongNgheCPU"].Value = tt.CongngheCPU;
            string masp = "Select Max(MaSP) from Sanpham";
            SqlCommand cmd1 = new SqlCommand(masp,kn.conn);
            SqlDataReader reader = cmd1.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                cmd.Parameters["@MaSP"].Value = reader.GetInt32(0);
            }// SqlDbType.VarChar);
            cmd1.Dispose();
            reader.Dispose();
            cmd.Parameters["@LoaiCPU"] .Value = tt.LoaiCPU;
            cmd.Parameters["@DungLuongRAM"].Value = tt.DungLuongRAM;
            cmd.Parameters["@DungLuongODia"].Value = tt.DuongLuongDia;
            cmd.Parameters["@KichThuocMH"].Value = tt.KichThuocMH;
            cmd.Parameters["@CardDoHoa"].Value = tt.CarDoHoa;
            cmd.Parameters["@KetNoiMang"].Value = tt.KetNoiMang;
            cmd.Parameters["@TichHop"].Value = tt.TichHop;
            cmd.Parameters["@HeDieuHanh"].Value = tt.HeDieuHanh;
            cmd.Parameters["@TrongLuong"].Value = tt.TrongLuong;
            cmd.Parameters["@ThongTinPin"].Value = tt.ThongTinPin;
            cmd.Parameters["@KhuyenMai"].Value = tt.KhuyenMai;
            cmd.Parameters["@XuatXu"].Value = tt.XuatXu;
            cmd.Parameters["@ThoiGianBH"].Value = tt.ThoiGianSD;
            cmd.Parameters["@Keyboard"].Value = tt.KeyBoard;


            cmd.ExecuteNonQuery();
            kn.Close_Connection();
        }

        public void ThemSanPham(DTO_SanPham sp )
        {           

            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql3 = "insert into SanPham (IDThuongHieu, TenSp, Anh, Gia, TinhTrang, BaoHanh, SoLuong)  values (@IDThuongHieu, @TenSp, @Anh, @Gia, @TinhTrang, @BaoHanh, @SoLuong)";
            SqlCommand cmd = new SqlCommand(sql3,kn.conn);

            cmd.Parameters.Add("@IDThuongHieu", SqlDbType.Int).Value = sp.ThuongHieu  ;
            cmd.Parameters.Add("@TenSp", SqlDbType.NVarChar).Value = sp.TenSanPham ;
            cmd.Parameters.Add("@Anh", SqlDbType.NVarChar).Value = sp.Anh;
            cmd.Parameters.Add("@Gia", SqlDbType.Float).Value = sp.Gia;
            cmd.Parameters.Add("@TinhTrang", SqlDbType.NVarChar).Value = sp.TinhTrang;
            cmd.Parameters.Add("@BaoHanh", SqlDbType.NVarChar).Value =sp.ThoiGanBH ;
            cmd.Parameters.Add("@SoLuong", SqlDbType.Int).Value = sp.SoLuong;
            //cmd.Parameters.Add("@IDThongTinSP", SqlDbType.Int).Value = sp.IDThongTin;
            cmd.ExecuteNonQuery();
            kn.Close_Connection();
             // de them thong tin san pham
        }

        public void CapNhat_SanPham(DTO_SanPham sp)
        {
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql3 = "Update SanPham set IDThuongHieu=@IDThuongHieu, TenSp=@TenSp, Anh=@Anh, Gia=@Gia, TinhTrang=@TinhTrang, BaoHanh=@BaoHanh, SoLuong=@SoLuong where MaSP=@MaSP";
            SqlCommand cmd = new SqlCommand(sql3, kn.conn);

            cmd.Parameters.Add("@MaSP", SqlDbType.Int).Value = sp.IDSanPham;
            cmd.Parameters.Add("@IDThuongHieu", SqlDbType.Int).Value = sp.ThuongHieu;
            cmd.Parameters.Add("@TenSp", SqlDbType.NVarChar).Value = sp.TenSanPham;
            cmd.Parameters.Add("@Anh", SqlDbType.NVarChar).Value = sp.Anh;
            cmd.Parameters.Add("@Gia", SqlDbType.Float).Value = sp.Gia;
            cmd.Parameters.Add("@TinhTrang", SqlDbType.NVarChar).Value = sp.TinhTrang;
            cmd.Parameters.Add("@BaoHanh", SqlDbType.NVarChar).Value = sp.ThoiGanBH;
            cmd.Parameters.Add("@SoLuong", SqlDbType.Int).Value = sp.SoLuong;
            //cmd.Parameters.Add("@IDThongTinSP", SqlDbType.Int).Value = sp.IDThongTin;
            cmd.ExecuteNonQuery();
            kn.Close_Connection();
        }
    }
}
